package org.hcf.utils.excel.poi.base.exp.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Collection;
import java.util.Iterator;
import java.util.Objects;

/**
 * @author changfu.he
 * @date 2021/9/4
 */
public class ValidationDataUtils {

    /**
     * @param wb xls 的workbook 对象
     * @param sheetName sheet 的名字
     * @param sheetHiden 创建的sheet 是否隐藏？
     * @param validationDatas
     * @return
     */
    public static Sheet createValidationDataSheet(Workbook wb,
                                                  String sheetName,
                                                  Boolean sheetHiden,
                                                  Collection<String> validationDatas){
        // 创建下拉列表值存储工作表
        Sheet sheet  = wb.createSheet(sheetName);
        if ( Objects.nonNull(sheetHiden) ) {
            wb.setSheetHidden(wb.getSheetIndex(sheet), sheetHiden);
        }
        // 循环往该sheet中设置添加下拉列表的值
        Iterator<String> iterator = validationDatas.iterator();
        int i=0;
        while (iterator.hasNext()){
            String nextVal = iterator.next();
            Row row = sheet.createRow(i);
            Cell cell = row.createCell((int) 0);
            cell.setCellValue(nextVal);
            i++;
        }
        return sheet;
    }

    /**
     * 创建 DataValidation
     * @param sourceSheet 源Sheet
     * @param strFormula 来源格式 如：ColumnValidationDataHandler.dataValidationStr
     * @param firstRow 下拉选从第几行开始
     * @param endRow 下拉选从第几行结束
     * @param firstCol 下拉选从第几列开始
     * @param endCol  下拉选从第几列结束
     * @return
     */
    public static DataValidation createDataValidation(
            Sheet sourceSheet,
            String strFormula,
            int firstRow,
            int endRow,
            int firstCol,
            int endCol){
        //
        DataValidationHelper dvHelper = sourceSheet.getDataValidationHelper();
        DataValidationConstraint formulaListConstraint = dvHelper.createFormulaListConstraint(strFormula);
        // DataValidation 范围
        int physicalNumberOfRows = sourceSheet.getPhysicalNumberOfRows();
        if(physicalNumberOfRows>endRow){
            endRow = physicalNumberOfRows-1;
        }
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = dvHelper.createValidation(formulaListConstraint, regions);
        // 数据校验
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);

        return dataValidation;
    }

    public static void setDataValidation(Workbook wb,
                                         Sheet sheet,
                                         String validationDataSheetName,
                                         Boolean sheetHiden,
                                         Collection<String> validationDatas,
                                         int firstRow,
                                         int endRow,
                                         int firstCol,
                                         int endCol){
        // 创建 隐藏 sheet 做数据源
        Sheet validationDataSheet = createValidationDataSheet(wb, validationDataSheetName, sheetHiden, validationDatas);
        // 来源格式获取
        String strFormula = dataValidationStr(validationDataSheetName, validationDatas.size());
        // 创建 数据下拉选
        DataValidation dataValidation = createDataValidation(sheet, strFormula, firstRow, endRow, firstCol, endCol);
        // 设置 sheet
        sheet.addValidationData(dataValidation);
    }

    /**
     * 表示A列 1-endRow 行作为下拉列表来源数据
     * {validationDataSheetName}!$A$1:$A${endRow}
     * @param validationDataSheetName
     * @param endRow
     * @return
     */
    public static String dataValidationStr(String validationDataSheetName,int endRow){
        StringBuilder strFormula = new StringBuilder();
        strFormula.append(validationDataSheetName)
                .append("!")
                .append("$A$1")
                .append(":")
                .append("$A$").append(endRow);
        return strFormula.toString();
    }

}
